<?php

function phpexcel_page($form_state){
	$form['upexcel'] = array(
	    '#type' => 'file',
	    '#title' => '上传Excel',
	    '#upload_location'=>'temporary://uploads',
	);
	$form['submit']=array(
		'#type' => 'submit',
	    '#value' => '提交',
	    '#submit'=>array('excel_up_submit'),	    
	);

	return $form;
}
function excel_up_submit($form,&$form_state){
	 set_time_limit(0); 
  	 $timestamp = time(); 
  	 $validators = array(
  		'file_validate_extensions' => array('xlsx xls'),
	);
  	 if($file=file_save_upload('upexcel',$validators)){
  	 	$row=0;
  	 	$paseRows = 0; 
	    $insertRows = 0; 
	    $table = array(
	      'name','status','rating','description','department','line','i_e',
	    );
	   
		require(drupal_get_path('module','excel').'/PHPExcel/PHPExcel/IOFactory.php');

	  	$filepath=substr($file->uri, 12);
	  	
	  	if(($handle = fopen (  file_directory_temp().'/'.$filepath , "r" )) !== FALSE){
	  		
	  		 $PHPExcel = new PHPExcel ();
	  		 
	  		 $PHPReader = new PHPExcel_Reader_Excel2007 ();
		  		 if(! $PHPReader->canRead ( file_directory_temp().'/'.$filepath )){
		  		 	$PHPReader = new PHPExcel_Reader_Excel5 ();
		  		 if(! $PHPReader->canRead(  file_directory_temp().'/'.$filepath  )){
		  		 	echo 'no excel';
		  		 	return;
	  		 	} 
	  		 }
	  	$PHPExcel = $PHPReader->load ( file_directory_temp().'/'.$filepath );
	  	$currentSheet = $PHPExcel->getSheet ( 0 );//获取当前
	  	$allColumn = $currentSheet->getHighestColumn();
	  	$col = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn());
	  	$allRow = $currentSheet->getHighestRow();
	  	for($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) {
	  		$token_db = $row_db = $field = array();
       		$i = 0;
       		$query = '';
       		for($colIndex = 0; $colIndex < $col; $colIndex++) {
       			$cell = $currentSheet->getCellByColumnAndRow($colIndex, $rowIndex)->getValue();
		          $cell = trim($cell);
		          if($cell instanceof PHPExcel_RichText) {
		            //富文本转换字符串
		            $cell = $cell->__toString();
		          }
		          if ($colIndex == 'A' && !intval($cell)) {
		            $paseRows++;
		            break;
		          }
		          $field[] = $table[$i];
		          //$token_db[] = "'%s'";
		         
		          if($cell!=''){
		          	 $row_db[] = $cell;
		          }else{
		          	$row_db[] = '-';
		          }
		          $i++;
       		}
       		$row++;
       		if ($row_db) {
	          $result=db_insert('excel')->fields(array_combine($field, $row_db))->execute();	         
	          $insertRows++;
	        }
	  	}	 
	  		 fclose ( $handle );
	  	}
	   drupal_set_message(t('文件 @file 导入成功.', array('@file' => $file->filename)));
    drupal_set_message("解析".$row."条数据完毕，新增共".$insertRows."条数据");
  	 }else {
   	drupal_set_message(t('File to import not found.'), 'error');
    return;
  }
}
